package com.bookyourtrek.model;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import com.bookyourtrek.util.DbConnection;
import com.bookyourtrek.util.RuntimeSettings;

public class TicketModel {

	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private String sqlQuery = "";
	private Connection conn = null;
	/**
	 * function to insert ticket details
	 * @author ananda
	 * @param trekid
	 * @param eVENTTREKKING
	 * @param tkttype
	 * @param tktamount
	 * @param tktquantity
	 * @param tktdescription
	 * @param tktenddate
	 * @param tktallowedperorder
	 * @throws ParseException 
	 */
	public void inserttkt(BigInteger trekid, String eventtype,
			String tkttype, String tktamount, Integer tktquantity,
			String tktdescription, String tktenddate, Integer tktallowedperorder) throws ParseException {
		sqlQuery = "INSERT INTO `bookyourtrek`.`ticket`"
				+ " (`eventid`, `eventtype`, `tickettype`, `amount`, `quantity`," +
				" `description`, `enddate`, `ticketallowedperorder`) VALUES (?,?,?,?,?,?,?,?);";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, trekid.longValue());
			ps.setString(2, eventtype);
			ps.setString(3, tkttype);
			ps.setDouble(4, Double.parseDouble(tktamount));
			ps.setInt(5, tktquantity);
			ps.setString(6, tktdescription);
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yy");
			
			java.sql.Date fdate = new java.sql.Date(sdf.parse(tktenddate).getTime());
			ps.setDate(7, fdate);
			ps.setInt(8, tktallowedperorder);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
	}
	public ArrayList<TicketPojo> getticket(BigInteger trekid, String eventtype) {
		ArrayList<TicketPojo> tktpojo = new ArrayList<TicketPojo>();
		sqlQuery = "SELECT ticketid, eventid, eventtype, tickettype," +
				" amount, quantity, description, enddate, ticketallowedperorder" +
				" FROM bookyourtrek.ticket"
				+ " where eventtype = ? AND eventid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setString(1, eventtype);
			ps.setLong(2, trekid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				TicketPojo tp = new TicketPojo();
				tp.setTicketid(BigInteger.valueOf(rs.getLong(1)));
				tp.setEventid(BigInteger.valueOf(rs.getLong(2)));
				tp.setEventtype(rs.getString(3));
				tp.setTickettype(rs.getString(4));
				tp.setAmount(rs.getDouble(5));
				tp.setQuantity(rs.getInt(6));
				tp.setDescription(rs.getString(7));
				//SimpleDateFormat toFormat = new SimpleDateFormat("dd-MM-yy");
				tp.setEnddate((rs.getDate(8)).toString());
				tp.setTicketallowedperorder(rs.getInt(9));
				tktpojo.add(tp);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return tktpojo;
	}
	
	public Integer getbookedtikcetquantity(BigInteger ticketid) {
		Integer totalbookedquantity = 0;
		sqlQuery = "SELECT SUM(quantity) FROM bookyourtrek.order"
				+ " where ticketid = ? AND status = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, ticketid.longValue());
			ps.setLong(2, RuntimeSettings.ORDERCONFIRMED);
			rs = ps.executeQuery();
			while (rs.next()) {
				totalbookedquantity = rs.getInt(1);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return totalbookedquantity;
}
	public Integer gettotalticketquantity(BigInteger ticketid) {
		Integer totalquantity = 0;
		sqlQuery = "SELECT quantity FROM bookyourtrek.ticket"
				+ " where ticketid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, ticketid.longValue());
			rs = ps.executeQuery();
			while (rs.next()) {
				totalquantity = rs.getInt(1);
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return totalquantity;
}
	public BigInteger gettotalticketssold() {
		BigInteger totalticketsold = null;
		sqlQuery = "SELECT SUM(quantity) FROM bookyourtrek.order";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			rs = ps.executeQuery();
			while (rs.next()) {
				totalticketsold = BigInteger.valueOf(rs.getInt(1));
			}		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		return totalticketsold;
	}
	
	/**
	 * function to update tkt table
	 * @author ananda
	 * @param trekkingid
	 * @param string
	 * @param tkttype
	 * @param tktamount
	 * @param tktquantity
	 * @param tktdescription
	 * @param tktenddate
	 * @param tktallowedperorder
	 * @param ticketid
	 * @throws ParseException 
	 */
	public void updatetkt(BigInteger trekkingid, String eventtype, String tkttype,
			String tktamount, Integer tktquantity, String tktdescription,
			String tktenddate, Integer tktallowedperorder, BigInteger ticketid) throws ParseException {
		
		sqlQuery = "UPDATE bookyourtrek.ticket SET eventid = ?, eventtype = ?, " +
				"tickettype = ?, amount = ?, quantity = ?, description = ?, " +
				"enddate = ?, ticketallowedperorder = ? WHERE ticketid = ?;";
		conn = DbConnection.getConnection();
		try {
			ps = conn.prepareStatement(sqlQuery);
			ps.setLong(1, trekkingid.longValue());
			ps.setString(2, eventtype);
			ps.setString(3, tkttype);
			ps.setDouble(4, Double.parseDouble(tktamount));
			ps.setInt(5, tktquantity);
			ps.setString(6, tktdescription);
			SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yy");
			System.out.println(sdf.parse(tktenddate).getTime());
			java.sql.Date fdate = new java.sql.Date(sdf.parse(tktenddate).getTime());
			ps.setDate(7, fdate);
			ps.setInt(8, tktallowedperorder);
			ps.setLong(9, ticketid.longValue());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			
		}
		
	}
}
